package school;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class JDBCOptionDAO implements OptionDAOInterface {
	Context namingContext;
	DataSource dataSource;
	Connection con = null;
	
	String sql = "select s.course_code, c.title, s.date_start, s.date_end, t.city, s.id from t_session s, t_course c, t_location t where s.course_code = c.code and s.location_id = t.id";
	String sql2 = " order by s.course_code";
	
	public JDBCOptionDAO() {
		try {
			Context namingContext = new InitialContext();
			DataSource dataSource = (DataSource)namingContext.lookup("java:comp/env/jdbc/schoolDatasource");
			con = dataSource.getConnection();
			System.out.println("Succes");
			con.setAutoCommit(false);
			
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Fail");
		}
	}

	@Override
	public Set<Option> getOptionList() {
		Set<Option> list = new HashSet<Option>();
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql+sql2);   
			
			while(rs.next()) {
				Option option = new Option();
				option.setCode(rs.getString(1));
				option.setTitle(rs.getString(2));
				option.setDate_start(rs.getString(3));
				option.setDatae_end(rs.getString(4));
				option.setLocation(rs.getString(5));
				option.setSession_id(rs.getInt(6));
				list.add(option);	
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} 
		
		return list;
	}

	@Override
	public Set<Option> getListByCourseCode(String code) {
		Set<Option> list = new HashSet<Option>();
		
		try {
			String sql1 = " and s.course_code = '?'";
			PreparedStatement pstmt = con.prepareStatement(sql+sql1+sql2);
			pstmt.setString(1, code);
			ResultSet rs = pstmt.executeQuery();   
			
			while(rs.next()) {
				Option option = new Option();
				option.setCode(rs.getString(1));
				option.setTitle(rs.getString(2));
				option.setDate_start(rs.getString(3));
				option.setDatae_end(rs.getString(4));
				option.setLocation(rs.getString(5));
				option.setSession_id(rs.getInt(6));
				list.add(option);	
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} 

		return list;
	}

	@Override
	public void create(Option option) {
		
		
	}

	@Override
	public void update(Option option) {
		
		
	}

	@Override
	public void delete(Option option) {
		try {
			String sql1 = "delete from t_session where id = '?'";
			PreparedStatement pstmt = con.prepareStatement(sql1);
			pstmt.setInt(1, option.getSession_id());
			pstmt.executeUpdate();  
			
		} catch(SQLException e) {
			e.printStackTrace();
		} 
	}

	@Override
	public Set<String> getLocations() {
		Set<String> listLoc = new HashSet<String>();
		
		try {
			String sql1 = "select CITY from t_location order by CITY";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql1);  
			
			while(rs.next()) {
				listLoc.add(rs.getString(1));
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} 
		
		return listLoc;
	}

	@Override
	public void deleteCourse(String code) {
		
		try {
			String sql1 = "delete from t_course where code = '?'";
			PreparedStatement pstmt = con.prepareStatement(sql1);
			pstmt.setString(1, code);
			pstmt.executeUpdate(); 
			
		} catch(SQLException e) {
			e.printStackTrace();
		} 
		
	}

	@Override
	public Set<Option> getListByDate(String date) {
		Set<Option> list = new HashSet<Option>();
		
		try {
			String sql1 = " and s.date_start < '?' and s.date_end > '?'";
			PreparedStatement pstmt = con.prepareStatement(sql+sql1+sql2);
			pstmt.setString(1, date);
			pstmt.setString(2, date);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				Option option = new Option();
				option.setCode(rs.getString(1));
				option.setTitle(rs.getString(2));
				option.setDate_start(rs.getString(3));
				option.setDatae_end(rs.getString(4));
				option.setLocation(rs.getString(5));
				option.setSession_id(rs.getInt(6));
				list.add(option);
			}
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Set<Option> getListByLocation(String loc) {
		Set<Option> list = new HashSet<Option>();
		
		try {
			String sql1 = " and t.city = '?'";
			PreparedStatement pstmt = con.prepareStatement(sql+sql1+sql2);
			pstmt.setString(1, loc);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				Option option = new Option();
				option.setCode(rs.getString(1));
				option.setTitle(rs.getString(2));
				option.setDate_start(rs.getString(3));
				option.setDatae_end(rs.getString(4));
				option.setLocation(rs.getString(5));
				option.setSession_id(rs.getInt(6));
				list.add(option);
			}
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Set<Option> getListBySearch(String keyWord, String dateStart, String loc) {
		Set<Option> list = new HashSet<Option>();
		String sql1 = "";
		
		if(keyWord != ""){
			sql1 += " and s.course_code like '%" + keyWord + "%'";
		}
		if(dateStart != ""){
			sql1 += " and s.date_start < '" + dateStart + "' and s.date_end > '" + dateStart + "'";
		}
		if(loc != ""){
			sql1 += " and t.city like '" + loc + "'";
		}
		
		try {
			Statement stmt = con.createStatement();	
			ResultSet rs = stmt.executeQuery(sql + sql1 + sql2);
			
			while(rs.next()) {
				Option option = new Option();
				option.setCode(rs.getString(1));
				option.setTitle(rs.getString(2));
				option.setDate_start(rs.getString(3));
				option.setDatae_end(rs.getString(4));
				option.setLocation(rs.getString(5));
				option.setSession_id(rs.getInt(6));
				list.add(option);
			}
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public Set<Option> getListCoursesRegistered(int[] sessionIds) {
		Set<Option> list = new HashSet<Option>();
		
		String sql1 = " and s.id IN (";
		for(int i = 0; i < sessionIds.length; i++){
			if (i == sessionIds.length - 1){
				sql1 += sessionIds[i] + ")";
			}
			else {
				sql1 += sessionIds[i] + ",";
			}
		}
		
		try {
			Statement stmt = con.createStatement();	
			ResultSet rs = stmt.executeQuery(sql + sql1 + sql2);
			
			while(rs.next()) {
				Option option = new Option();
				option.setCode(rs.getString(1));
				option.setTitle(rs.getString(2));
				option.setDate_start(rs.getString(3));
				option.setDatae_end(rs.getString(4));
				option.setLocation(rs.getString(5));
				option.setSession_id(rs.getInt(6));
				list.add(option);
			}
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public boolean sessionExistence(int clientId, int sessionId) {
		boolean indice = false;
		try {
			String sql1 = "select * from t_client_session where client_id = " + clientId + " and session_id =" + sessionId;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql1);
			if (rs.next()){
				indice = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return indice;
	}

	@Override
	public void addSessionToClient(int clientId, int sessionId) {
		
		try {
			String sql1 = "insert t_client_session(client_id,session_id) values(" + clientId + ", " + sessionId + ")";
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql1);
			con.commit();
	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public Set<Option> getSessionsByClient(int clientId) {
		String sql1 = " and s.id IN (select session_id from t_client_session where client_id = " + clientId + ")";
		Set<Option> list = new HashSet<Option>();
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql+sql1+sql2);
			
			while(rs.next()) {
				Option option = new Option();
				option.setCode(rs.getString(1));
				option.setTitle(rs.getString(2));
				option.setDate_start(rs.getString(3));
				option.setDatae_end(rs.getString(4));
				option.setLocation(rs.getString(5));
				option.setSession_id(rs.getInt(6));
				list.add(option);
			}
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return list;
	}


}
